package com.gy.boot.other;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.gy.boot.common.util.BlankUtil;
import com.gy.boot.common.util.CommonUtil;
import com.gy.boot.common.util.StrTool;
import org.apache.poi.ss.usermodel.CellStyle;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class LxyExcel {

    /** 处理第2列序号 */
    public static void updateXH(String new_path, String sheetName, String pre, int rows) {
        List<Map<String, Object>> list = new ArrayList<>();
        int a = 1; int x = 1; int y = 0;
        for (int i = 0; i < 20000; i++) {
            if (a > rows) { x++; a=1; } a++; // 每rows行共用一个编号
            Map<String, Object> v = MapUtil.newHashMap(4);
            v.put("y", i);
            v.put("x0", pre+x);
            list.add(v);
        }
        writerXH(new_path, sheetName, list);
    }

    /** 处理第一列序号 */
    public static void updateXH(String new_path, String sheetName) {
        System.out.println("步骤2: 读取原始文件序号");
        List<Map<String, Object>> list = new ArrayList<>();
        ExcelUtil.readBySax(new_path, sheetName, readRowByUpdateXH(list));
        System.out.println(JSONUtil.toJsonStr(list));
        System.out.println(list.size());
        System.out.println("步骤3: 处理新序号");
        initListXH(list);
        System.out.println(JSONUtil.toJsonStr(list));
        // 步骤4: 写入新序号
        if (list.isEmpty()) { return; }
        System.out.println("步骤4: 写入新序号");
        writerXH(new_path, sheetName, list);
    }
    private static void writerXH(String new_path, String sheetName, List<Map<String, Object>> list) {
        ExcelWriter writer = ExcelUtil.getWriter(new_path, sheetName);
        CellStyle csB3 = writer.getOrCreateCellStyle("B3");
        for (Map<String, Object> v : list) {
            if (v == null || v.isEmpty()) { return; }
            int x = 0; int y = StrTool.valueOfInt(v.get("y"));
            writer.writeCellValue(x, y, v.get("x0"));
            writer.setStyle(csB3, x, y);
        }
        writer.close();
        System.out.println("更新序号完毕,请检查!!!");

    }

    /** 读取第一列数据 */
    private static RowHandler readRowByUpdateXH(List<Map<String, Object>> list) {
        return (sheetIndex, rowIndex, rowList) -> {
            if (rowIndex < 2) { return; }
            String x0 = CommonUtil.getValue(rowList, 0); // A列值-可以无值
            String x1 = CommonUtil.getValue(rowList, 1); // B列值-必须有值
            if (BlankUtil.isBlankNull(x1)) { return; }
            Map<String, Object> params = MapUtil.newHashMap();
            params.put("x0", x0); // A列值
            params.put("y", rowIndex);
            list.add(params);
        };
    }
    /** 处理新序号 */
    private static void initListXH(List<Map<String, Object>> list) {
        if (list == null || list.isEmpty()) { return; }
        int a = 1; int x = 1; int y = 0;
        for (Map<String, Object> v : list) {
            if (v == null || v.isEmpty()) { continue; }
            String x0 = StrTool.valueOf(v.get("x0"));
            if ("空置".equals(x0)) { continue; }
            if (a > 5) { x++; a=1; } a++; // 每5行共用一个编号
            v.put("x0", getStrX(x));
            y = StrTool.valueOfInt(v.get("y"));
        }
        // 新增到2万行
        for (int i = y+3; i <= 20000; i++) {
            if (a > 5) { x++; a=1; } a++; // 每5行共用一个编号
            Map<String, Object> v = MapUtil.newHashMap(4);
            v.put("y", i);
            v.put("x0", getStrX(x));
            list.add(v);
        }
    }
    /** 获取序号字符串:补0 */
    private static String getStrX(int x) {
        if (x >= 100) { return String.valueOf(x); }
        if (x >= 10) { return "0"+x; }
        if (x >= 1) { return "00"+x; }
        return "";
    }

}
